#!/usr/bin/env python
# coding: utf-8

# In[1]:


import pandas as pd
import pymysql
import numpy as np


# 数据读取
host = "localhost"
user = "root"
passwd = "392892"
db = "course"
sql = "select * from 5_task;"
try:
    conn = pymysql.connect(host=host, user=user, passwd=passwd,db=db,charset="utf8")
    cursor = conn.cursor()
    print("数据库连接成功！")
    cursor.execute(sql)
    res = cursor.fetchall()
    print("SQL执行成功！")
    cols = [cursor.description[i][0] for i in range(len(cursor.description))]
    df = pd.DataFrame(res, columns=cols)
    print("完成！")
except Exception as e:
    print(e)


# In[2]:


df2 = df.copy()

# 选择用户资金流量数据
df2 = df2[["user_id", "report_date","tBalance", "yBalance"]]
user_info = df2["user_id"].value_counts()
user_ID = user_info.index
freq = user_info.values
df_user = pd.DataFrame(list(zip(user_ID, freq)), columns=["用户ID", "频次"])
df_user = df_user[df_user["频次"] > 400]  # 过滤掉出现频次低于阈值的客户。
ID = df_user["用户ID"][0]
df_s = df2[df2["user_id"] == ID].reset_index(drop=True)  # 选择想要预测的客户ID
df_s["report_date"] = pd.to_datetime(df_s["report_date"].map(str)) # 日期格式处理
df_s = df_s[["report_date","tBalance"]].drop_duplicates().sort_values(by="report_date", ascending=True, ignore_index=True).set_index("report_date")


# #### 白噪声检验

# In[8]:


import statsmodels.api as sm
from statsmodels.stats.diagnostic import acorr_ljungbox
display("原始数据纯随机性检验：",acorr_ljungbox(df_s, lags = [6, 12], boxpierce = True))
display("一阶差分数据纯随机性检验：",acorr_ljungbox(df_s.diff()[1:], lags = [6, 12], boxpierce = True)) # 白噪声检验

